from flask import Blueprint, render_template, request, redirect, url_for, session
import json
import pymysql
from flask import request
from config import Config
# from sqlalchemy import *
# from model.user import User
# # from model.china_job import China_job
# # from flask_sqlalchemy import SQLAlchemy
# db = SQLAlchemy()
ps = Blueprint('personal', __name__)
# 注册
@ps.route("/signIn", methods=['POST', 'GET'])
def personal_sign_in():
    if request.method == 'GET':
        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        cursor.execute('select * from job_education')
        education = cursor.fetchall()
        connect.commit()

        cursor.execute('select * from job_position')
        position = cursor.fetchall()
        connect.commit()

        return render_template('signIn.html',education=education,position=position)

    if request.method == 'POST':
        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        data = json.loads(request.data.decode())
        user_id = data["user_id"]
        user_name = data["user_name"]
        password = data["password"]
        e_mail = data["e_mail"]
        education = data["education"]
        work_experience = data["work_experience"]
        current_work = data["current_work"]
        current_pay = data["current_pay"]
        foretell_work01 = data["foretell_work01"]
        foretell_work02 = data["foretell_work02"]
        foretell_work03 = data["foretell_work03"]
        tableSql = "select user_id  from `user`  where user_id = %s"
        cursor.execute(tableSql, (str(user_id)))
        user_flag = cursor.fetchall()
        connect.commit()
        if len(user_flag) != 0:
           return "user_exist"
        else:
            insertUser = "insert into `user` value (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
            cursor.execute(insertUser, (user_id, user_name, password, '0', e_mail, education,
                                        work_experience, current_work, current_pay,
                                        foretell_work01, foretell_work02, foretell_work03))

            cursor.execute('select user_id  from `user`  where user_id = %s', (str(user_id)))

            user_flag = cursor.fetchall()
            connect.commit()
            if len(user_flag) != 0:
                session['power'] = 0
                session["news"] = "0"
                session['user_id'] = user_id
                return 'success'
            else:
                session['power'] = -1
                render_template('error.html')


@ps.route("/personal")
def personal_centre():
    try:
        if session["power"] >= 0:
            pass
    except Exception:
        return render_template('login.html', tip="没有权限,请先登录")
    else:
        return render_template('/admin/personal_index.html')

# 个人报告
@ps.route("/personal/report")
def report():
    # 判断是否已登录
    if session.get('power') == None:
        session['power'] = -404
        return redirect(url_for('index'))
    # 判断session中admin的状态是否为成功登录
    if session['power'] >= 0:
        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        # job_rows = db.session.query(User.foretell_work01, User.foretell_work02, User.foretell_work03).\
        #     filter(User.user_id == ).all()
        cursor.execute('select foretell_work01,foretell_work02,foretell_work03 from `user` where user_id = %s',
                       (session.get('user_id'),))
        job_rows = cursor.fetchall()
        jobs = tuple(map(lambda x: (x[0], x[1], x[2]), job_rows))[0]
        if len(job_rows) == 0:
            return render_template('power404.html')
        # 图2
        h_bar_data = []
        # h_bar_rows = db.session.query(China_job.position,
        #                               (func.avg(func.SUBSTRING_INDEX(China_job.salary, "-", 1)
        #                                         + func.SUBSTRING_INDEX(China_job.salary, "-", -1)) / 2).label(
        #                                   's')). \
        # filter(or_(China_job.position == jobs[0], China_job.position == jobs[1], China_job.position == jobs[2])).\
        # group_by(China_job.position).order_by(desc('s')).all()
        cursor.execute('select position, avg((substring_index(salary, "-", 1)+substring_index(salary, "-", -1))/2) as s'
                       ' from china_job where position = %s or position = %s or position = %s'
                       ' group by position order by s desc', jobs)
        h_bar_rows = cursor.fetchall()
        for row in h_bar_rows:
            h_bar_data.append({'name': row[0], 'value': round(row[1], 2)})
        h_bar_data.sort(key=lambda x: x['value'], reverse=True)

        # 图1
        bar_axis = []
        bar_data = []
        # 获取第一个工种, 各个城市的平均薪资
        # bar_rows = db.session.query(China_job.province, China_job.city, China_job.position,
        # (func.avg(func.SUBSTRING_INDEX(China_job.salary, "-", 1)+
        # func.SUBSTRING_INDEX(China_job.salary, "-", -1)) / 2).label('s')).\
        #     filter(and_(China_job.position == h_bar_data[0]['name'], China_job.salary != "面议")).\
        #     group_by(China_job.city, China_job.province, China_job.position).order_by(desc('s')).all()[:10]
        cursor.execute('select province,city,position,'
                       ' avg((substring_index(salary, "-", 1)+substring_index(salary, "-", -1))/2) as s'
                       ' from china_job where position = %s and salary != "面议" group by city,province,position'
                       ' order by s desc limit 10', (h_bar_data[0]['name'],))
        bar_rows = cursor.fetchall()
        for item in bar_rows:
            bar_axis.append(item[0]+'-'+item[1])
            bar_data.append(round(item[3], 2))
        job_city = bar_rows[0][1]
        # 图3
        # pie_rows = db.session.query(China_job.city, func.count(China_job.position).label('c')). \
        #                          filter(China_job.position == h_bar_data[0]['name']). \
        #                          group_by(China_job.city, China_job.position).order_by(desc('c')).all()[:15]
        cursor.execute('select city, count(position) as c from china_job where position = %s group by city,position'
                       ' order by c desc limit 15', (h_bar_data[0]['name'],))
        pie_rows = cursor.fetchall()
        pie_data = []
        for row in pie_rows:
            pie_data.append({'name': row[0], 'value': row[1]})
        pie_city = pie_rows[0][0]
        # 图4

        # radar_rows = db.session.query(China_job.education, func.count(China_job.education).label('c')). \
        #     filter(China_job.position == h_bar_data[0]['name']).group_by(China_job.education). \
        #     order_by(desc('c')).all()[:10]
        cursor.execute('select education, count(education) as c from china_job'
                       ' where position = %s group by education order by c desc limit 10', (h_bar_data[0]['name'],))
        radar_rows = cursor.fetchall()
        cursor.execute('select education from `user` where user_id = %s limit 1', (session.get('user_id')))
        edu = cursor.fetchone()[0]
        # edu = db.session.query(User.education).filter(User.user_id == session.get('user_id') ).all()[:1][0][0]
        # 0 一样 大于0 高 小于0 低
        radar_edu = radar_rows[0][0]
        edu_dict = {'高中': 1, '技校': 2, '中专': 3, '大专': 4, '本科': 5, '不限': 0, '博士': 6, '硕士': 7,}
        edu_temp = edu_dict[edu] - edu_dict[radar_edu]

        if edu_temp >= 0:
            edu_status = "符合"
        else:
            edu_status = "未符合"

        radar_temp = {
            '不限': 0,
            '本科': 0,
            '大专': 0,
            '中专': 0,
            '技校': 0,
            '高中': 0,
            '博士': 0,
            '硕士': 0,
        }
        for row in radar_rows:
            radar_temp[row[0]] += int(row[1])
        radar_data = []
        radar_max = 0
        for key in radar_temp:
            radar_data.append(radar_temp[key])
            if radar_temp[key] > radar_max:
                radar_max = radar_temp[key]
        radar_indi = [
            {'name': '不限', 'max': radar_max},
            {'name': '本科', 'max': radar_max},
            {'name': '大专', 'max': radar_max},
            {'name': '中专', 'max': radar_max},
            {'name': '技校', 'max': radar_max},
            {'name': '高中', 'max': radar_max},
            {'name': '博士', 'max': radar_max},
            {'name': '硕士', 'max': radar_max},
        ]
        return render_template('/personal/report.html', job_name=h_bar_data[0]['name'], bar_axis=bar_axis,
                               bar_data=bar_data, job_city=job_city,
                               radar_indi=radar_indi, radar_data=radar_data,
                               radar_edu=radar_edu, edu_status=edu_status,
                               h_bar_data=h_bar_data, pie_data=pie_data, pie_city=pie_city)



# 个人首页/个人资料
@ps.route("/personal_details")
def personal_details():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                              port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()

    try:
        if session["power"] >= 0:
            user_id  =  session['user_id']
            selectSQL01 = """
                        select 
                         user_id,
                         user_name, 
                         e_mail ,
                         education, 
                         work_experience, 
                         current_work ,
                         current_pay ,
                         foretell_work01, 
                         foretell_work02 , 
                         foretell_work03
                         from `user`
                         where user_id = %s
                        """
            cursor.execute(selectSQL01, (str(user_id)))
            user = cursor.fetchall()[0]
            connect.commit()


            selectSQL02 = """
                           select  * 
                           from `job_education`
                           """
            cursor.execute(selectSQL02)
            education = cursor.fetchall()
            connect.commit()

            selectSQL03 = """
                           select  * 
                           from `job_position`
                           """
            cursor.execute(selectSQL03)
            position = cursor.fetchall()
            connect.commit()
    except Exception:
        return  render_template('login.html', tip="没有权限,请先登录")
    else:
        return render_template('/personal/personal_details.html',
                               user=user, education=education, position=position)


# 修改个人资料
@ps.route("/personal_alter", methods=['POST'])
def personal_alter():
    connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                              port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
    cursor = connect.cursor()
    data = json.loads(request.data.decode())
    user_id = data["user_id"]
    user_name = data["user_name"]
    e_mail = data["e_mail"]
    work_experience = data["work_experience"]
    current_pay = data["current_pay"]


    insertUser = """update  `user`   
    set user_name = %s,
     e_mail = %s,
     work_experience = %s,
     current_pay = %s
     where user_id = %s
    """
    cursor.execute(insertUser, ( user_name ,e_mail , work_experience ,current_pay,user_id))
    connect.commit()
    return "success"


# 修改个人资料-学历123
@ps.route('/personal_alter_education', methods=['POST'])
def personal_alter_education():
    if request.method == 'POST':

        data = json.loads(request.data.decode())
        content = str(data['content'])
        user_id =str(data['user_id'])

        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        updateData = """update  `user`
            set  education = %s
            where user_id = %s
            """
        cursor.execute(updateData, (content,user_id))
        connect.commit()
        return "success"
    else:
        return "fail"

# 修改个人资料-工作
@ps.route('/personal_alter_current_work', methods=['POST'])
def personal_alter_current_work():
    if request.method == 'POST':

        data = json.loads(request.data.decode())
        content = str(data['content'])
        user_id =str(data['user_id'])

        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        updateData = """update  `user`
            set  current_work = %s
            where user_id = %s
            """
        cursor.execute(updateData, (content,user_id))
        connect.commit()
        return "success"
    else:
        return "fail"


# 修改个人资料-预测工作01
@ps.route('/personal_alter_foretell_work01', methods=['POST'])
def personal_alter_foretell_work01():
    if request.method == 'POST':

        data = json.loads(request.data.decode())
        content = str(data['content'])
        user_id =str(data['user_id'])

        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        updateData = """update  `user`
            set  foretell_work01 = %s
            where user_id = %s
            """
        cursor.execute(updateData, (content,user_id))
        connect.commit()
        return "success"
    else:
        return "fail"


# 修改个人资料-预测工作02
@ps.route('/personal_alter_foretell_work02', methods=['POST'])
def personal_alter_foretell_work02():
    if request.method == 'POST':

        data = json.loads(request.data.decode())
        content = str(data['content'])
        user_id =str(data['user_id'])

        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        updateData = """update  `user`
            set  foretell_work02 = %s
            where user_id = %s
            """
        cursor.execute(updateData, (content,user_id))
        connect.commit()
        return "success"
    else:
        return "fail"

# 修改个人资料-预测工作03
@ps.route('/personal_alter_foretell_work03', methods=['POST'])
def personal_alter_foretell_work03():
    if request.method == 'POST':

        data = json.loads(request.data.decode())
        content = str(data['content'])
        user_id =str(data['user_id'])

        connect = pymysql.connect(host=Config.MYSQL_HOST, user=Config.MYSQL_USER, passwd=Config.MYSQL_PASSWD,
                                  port=Config.MYSQL_PORT, database=Config.MYSQL_DATABASE)
        cursor = connect.cursor()
        updateData = """update  `user`
            set  foretell_work03 = %s
            where user_id = %s
            """
        cursor.execute(updateData, (content,user_id))
        connect.commit()
        return "success"
    else:
        return "fail"



